using System;
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using Grebok.Domain;
using Grebok.Persistence;
using Grebok.Persistence.QueryModel;

namespace Grebok.Persistence.AdoNet
{
    internal class ShipperDataMapper : IDataMapper<Shipper>
    {

        private static readonly string insertShipperCommandText = "INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@CompanyName, @Phone)";
        private static readonly string updateShipperByCommandText = "UPDATE [Shippers] SET [CompanyName] = @CompanyName, [Phone] = @Phone WHERE [ShipperID] = @ShipperID";
        private static readonly string deleteShipperByIdCommandText = "DELETE FROM [Shippers] WHERE [ShipperID] = @ShipperID";

        #region IDataMapper Members

        public virtual void Create(Shipper item)
        {
            SqlHelper.ExecuteNonQuery(ProviderHelper.ConnectionString,
                CommandType.Text,
                insertShipperCommandText,
                GetParametersByEntity(item));
        }

        public virtual void Delete(Shipper item)
        {
            SqlParameter shipperIdParameter = new SqlParameter("@ShipperId", SqlDbType.NChar, 5);
            shipperIdParameter.Value = item.Id;
            SqlHelper.ExecuteNonQuery(ProviderHelper.ConnectionString,
                CommandType.Text,
                deleteShipperByIdCommandText,
                new SqlParameter[] { shipperIdParameter });
        }

        public virtual void Update(Shipper item)
        {
            SqlHelper.ExecuteNonQuery(ProviderHelper.ConnectionString,
                CommandType.Text,
                updateShipperByCommandText,
                GetParametersByEntity(item));
        }

        public virtual IList<Shipper> GetAll()
        {
            List<Shipper> shippers = new List<Shipper>();
            SqlDataReader shippersReader = SqlHelper.ExecuteReader(ProviderHelper.ConnectionString,
                                        CommandType.Text,
                                        "SELECT * FROM Shippers");
            while (shippersReader.Read())
            {
                shippers.Add(BuildDataObjectFromRawData(shippersReader));
            }
            shippersReader.Close();
            return shippers;
        }

        public virtual Shipper GetByKey(object shipperId)
        {
            Shipper shipper = new UnknownShipper();
            SqlParameter shipperIdParameter = new SqlParameter("@ShipperId", SqlDbType.NChar, 5);
            shipperIdParameter.Value = shipperId;
            SqlDataReader shippersReader = SqlHelper.ExecuteReader(ProviderHelper.ConnectionString,
                            CommandType.Text,
                            "SELECT * FROM Shippers WHERE ShipperId=@ShipperId",
                            new SqlParameter[] { shipperIdParameter });
            if (shippersReader.Read())
                shipper = BuildDataObjectFromRawData(shippersReader);
            shippersReader.Close();
            return shipper;
        }

        public IList<Shipper> GetByCriteria(Query query)
        {
            throw new NotImplementedException();
        }

        #endregion

        protected virtual Shipper BuildDataObjectFromRawData(IDataRecord rawData)
        {
            Shipper shipper = new Shipper();
            shipper.Id = (int)rawData["ShipperId"];
            shipper.CompanyName = (string)rawData["CompanyName"];
            shipper.Phone = rawData["Phone"] is DBNull ? string.Empty : (string)rawData["Phone"];
            return shipper;
        }


        protected virtual SqlParameter[] GetParametersByEntity(Shipper item)
        {
            SqlParameter shipperIdParameter = new SqlParameter("@ShipperId", SqlDbType.Int,4);
            shipperIdParameter.Value = item.Id;
            SqlParameter companyNameParameter = new SqlParameter("@CompanyName", SqlDbType.NVarChar, 40);
            companyNameParameter.Value = item.CompanyName;
            SqlParameter phoneParameter = new SqlParameter("@Phone", SqlDbType.NVarChar, 24);
            phoneParameter.Value = item.Phone;

            return new SqlParameter[]{
										 shipperIdParameter, 
										 companyNameParameter,
										 phoneParameter
									 };
        }


    }
}
